package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.management.RuntimeErrorException;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.DateUtils;
import com.bringway.p2p.constant.P2pConstant;
import com.bringway.p2p.dao.TjbbDao;
import com.bringway.p2p.util.NumberUtil;

import net.sf.json.JSONObject;

@Repository
public class TjbbDaoImpl extends P2PJdbcDao implements TjbbDao {
	private Logger logger = Logger.getLogger(TjbbDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryTjbbDayList(String tjDate) {
		Map<String, Object> paramMap = new HashMap<>();
		String tjDates[] = StringUtils.split(tjDate, '-');
		if (java.sql.Date.valueOf(tjDate).after(new Date())) {// 如果统计日期大于当前日期,返回空
			logger.info("queryTjbbDayList 统计日期大于当前日期,无数据:" + tjDate);
			return new ArrayList<>();
		}
		int tjDate_day = Integer.parseInt(tjDates[2]);// 获得统计日期是本月第几天
		for (int i = 1; i <= tjDate_day; i++) {// 循环生成本月数据,截止统计日期
			tjDates[2] = String.format("%02d", i);// 设置生成数据日期
			boolean isCacheTable = this.isCacheTable(StringUtils.join(tjDates, '-'));
			if (!isCacheTable) {// 如果不存在有效缓存数据,生成缓存表数据
				this.insertCacheTable(StringUtils.join(tjDates, '-'));
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT ID,date_format(TJ_DATE,'%Y-%m-%d') TJ_DATE,ifnull(GSMC,'无归属') GSMC,ifnull(MDMC,'/') MDMC,sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ,sum(ifnull(NHYZ_MONTH,0)) NHYZ_MONTH");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(JNHZ_MONTH,0)) JNHZ_MONTH");
		sql.append(" FROM t_zhfw_bb_tj T where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate");
		sql.append(" group by GSMC,MDMC order by CONVERT(GSMC USING gbk) desc");
		paramMap.put("tjDate", tjDate);
		logger.info("queryTjbbDayList sql is " + sql.toString());
		logger.info("queryTjbbDayList paramMap is " + paramMap);
		List<Map<String, Object>> result = this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
		// 获取当月的值
		StringBuffer sql_month = new StringBuffer();
		sql_month.append(
				"SELECT sum(ifnull(NHYZ,0)) NHYZ_M,sum(ifnull(NHYZ_MONTH,0)) NHYZ_MONTH_M,sum(ifnull(TBYZ,0)) TBYZ_M,sum(ifnull(JTBZ,0)) JTBZ_M");
		sql_month.append(
				",sum(ifnull(JNHZ,0)) JNHZ_M,sum(ifnull(JNHZ_MONTH,0)) JNHZ_MONTH_M FROM t_zhfw_bb_tj T where date_format(TJ_DATE,'%Y-%m')=:tjDateS AND date_format(TJ_DATE,'%Y-%m-%d')<=:tjDate");
		sql_month.append(" AND GSMC=:GSMC AND MDMC=:MDMC");
		paramMap.put("tjDateS", StringUtils.substring(tjDate, 0, 7));
		for (Map<String, Object> map : result) {
			String gsmc = MapUtils.getString(map, "GSMC");
			String mdmc = MapUtils.getString(map, "MDMC");
			if (StringUtils.equals(gsmc, "无归属") && StringUtils.equals(mdmc, "/")) {
				StringBuffer sql_temp = new StringBuffer();
				sql_temp.append(
						"SELECT sum(ifnull(NHYZ,0)) NHYZ_M,sum(ifnull(NHYZ_MONTH,0)) NHYZ_MONTH_M,sum(ifnull(TBYZ,0)) TBYZ_M,sum(ifnull(JTBZ,0)) JTBZ_M");
				sql_temp.append(
						",sum(ifnull(JNHZ,0)) JNHZ_M,sum(ifnull(JNHZ_MONTH,0)) JNHZ_MONTH_M FROM t_zhfw_bb_tj T where date_format(TJ_DATE,'%Y-%m')=:tjDateS AND date_format(TJ_DATE,'%Y-%m-%d')<=:tjDate");
				sql_temp.append(" AND GSMC is null AND MDMC is null");
				map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_temp.toString(), paramMap));
			} else {
				paramMap.put("GSMC", gsmc);
				paramMap.put("MDMC", mdmc);
				map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_month.toString(), paramMap));
			}
		}
		return result;
	}
	public List<Map<String, Object>> queryTjbbDayReport(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		//where TJ_DATE between '2016-07-04' and '2016-07-09';
		sql.append("select t.JTBZ l,date_format(t.TJ_DATE,'%Y-%m-%d')  date,t.JTBZ value from (  SELECT sum(JTBZ) JTBZ, TJ_DATE FROM t_zhfw_bb_tj  group by TJ_DATE ) t ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t.TJ_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t.TJ_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		
		logger.info("queryTjbbDayReport sql is " + sql.toString());
		logger.info("queryTjbbDayReport paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}
	@Override
	public Map<String, Object> getSumTjbbDay(String tjDate) {
		Map<String, Object> paramMap = new HashMap<>();
		String tjDates[] = StringUtils.split(tjDate, '-');
		if (java.sql.Date.valueOf(tjDate).after(new Date())) {// 如果统计日期大于当前日期,返回空
			logger.info("getSumTjbbDay 统计日期大于当前日期,无数据:" + tjDate);
			return new HashMap<>();
		}
		int tjDate_day = Integer.parseInt(tjDates[2]);// 获得统计日期是本月第几天
		for (int i = 1; i <= tjDate_day; i++) {// 循环生成本月数据,截止统计日期
			tjDates[2] = String.format("%02d", i);// 设置生成数据日期
			boolean isCacheTable = this.isCacheTable(StringUtils.join(tjDates, '-'));
			if (!isCacheTable) {// 如果不存在有效缓存数据,生成缓存表数据
				this.insertCacheTable(StringUtils.join(tjDates, '-'));
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ,sum(ifnull(NHYZ_MONTH,0)) NHYZ_MONTH");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(JNHZ_MONTH,0)) JNHZ_MONTH");
		sql.append(" FROM t_zhfw_bb_tj T where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate");
		paramMap.put("tjDate", tjDate);
		logger.info("getSumTjbbDay sql is " + sql.toString());
		logger.info("getSumTjbbDay paramMap is " + paramMap);
		Map<String, Object> result = this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
		// 获取当月合计的值
		StringBuffer sql_month = new StringBuffer();
		sql_month.append(
				"SELECT sum(ifnull(NHYZ,0)) NHYZ_M,sum(ifnull(NHYZ_MONTH,0)) NHYZ_MONTH_M,sum(ifnull(TBYZ,0)) TBYZ_M,sum(ifnull(JTBZ,0)) JTBZ_M");
		sql_month.append(
				",sum(ifnull(JNHZ,0)) JNHZ_M,sum(ifnull(JNHZ_MONTH,0)) JNHZ_MONTH_M FROM t_zhfw_bb_tj T where date_format(TJ_DATE,'%Y-%m')=:tjDateS AND date_format(TJ_DATE,'%Y-%m-%d')<=:tjDate");
		paramMap.put("tjDateS", StringUtils.substring(tjDate, 0, 7));
		result.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_month.toString(), paramMap));
		return result;
	}

	/**
	 * @作者 js
	 * @方法名 isCacheTable
	 * @功能 TODO 判断是否存在有效的统计缓存:(创建日期大于统计日期才能保证数据完整性)
	 * @param tjDate
	 * @return true:存在,false:不存在
	 */
	@SuppressWarnings("deprecation")
	private boolean isCacheTable(String tjDate) {
		String sql = "select count(*) from t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate and date_format(TJ_DATE,'%Y-%m-%d')<date_format(CREATE_DATE,'%Y-%m-%d')";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		boolean isCacheTable = this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap) > 0 ? true : false;
		logger.info("isCacheTable 判断是否存在有效的报表缓存,日期:" + tjDate + "  " + isCacheTable);
		return isCacheTable;
	}
	/**
	 * @作者 js  
	 * @方法名 deleteCache  
	 * @功能 TODO 删除缓存数据
	 * @param tjDate
	 */
	public void deleteCache(String tjDate) {
		logger.info("deleteCache 删除报表缓存,日期:" + tjDate + "...");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);// 当前统计日期,查询用
		// 删除表中可能存在的缓存数据
		String sql_del = "delete from t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate";
		this.getNamedParameterJdbcTemplate().update(sql_del, paramMap);
	}

	/**
	 * @作者 js
	 * @方法名 insertCacheTable
	 * @功能 TODO 生成当前统计日期的缓存数据
	 * @param tjDate
	 */
	public void insertCacheTable(String tjDate) {
		logger.info("insertCacheTable 开始生成报表缓存,日期:" + tjDate + "...");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);// 当前统计日期,查询用
		// 删除表中可能存在的缓存数据
		String sql_del = "delete from t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate";
		// logger.info("insertCacheTable sql_del is " + sql_del);
		this.getNamedParameterJdbcTemplate().update(sql_del, paramMap);
		// 获取用户列表
		StringBuffer sql_gyh = new StringBuffer();
		sql_gyh.append("SELECT T.USER_NAME FROM ");
		sql_gyh.append(
				"(SELECT USERID USER_NAME FROM t_zhfw_tzrxx where date_format(TZSJ,'%Y-%m-%d')=:tjDate and TCZT='成功' group by USERID ");
		sql_gyh.append(
				"union SELECT USER_ID USER_NAME FROM t_zhfw_hb where date_format(HB_USE_DATE,'%Y-%m-%d')=:tjDate and HB_STATE='1' group by USER_ID ");
		sql_gyh.append(
				"union SELECT GYH USER_NAME FROM t_zhfw_czjl where date_format(CZSJ,'%Y-%m-%d')=:tjDate group by GYH ");
		sql_gyh.append(
				"union SELECT GYH USER_NAME FROM t_zhfw_tx where date_format(TXSJ,'%Y-%m-%d')=:tjDate and TX_STATE='01' group by GYH ");
		sql_gyh.append(
				"union SELECT USER_NAME FROM t_zhfw_user where date_format(CJRQ,'%Y-%m-%d')=:tjDate and DISABLED='1') T group by T.USER_NAME");
		// logger.info("insertCacheTable sql_gyh is " + sql_gyh.toString());
		List<Map<String, Object>> list = new ArrayList<>();
		list = this.getNamedParameterJdbcTemplate().queryForList(sql_gyh.toString(), paramMap);
		// 获取缓存信息
		for (Map<String, Object> map : list) {
			paramMap.put("username", MapUtils.getString(map, "USER_NAME"));
			// 获取每个用户的投资信息:投标原值,年化原值(天),年化原值(月),红包抵用,净投标,净年化(天),净年化(月)
			StringBuffer sql_tzxx = new StringBuffer();
			// sql_tzxx.append("SELECT sum(t1.TCJE)
			// TBYZ,sum(round((t1.TCJE/365*t2.QX),2))
			// NHYZ,sum(round((t1.TCJE/12*t2.QX_MONTH),2))
			// NHYZ_MONTH,sum(ifnull(t3.HB_JE,0)) HBDY");
			// sql_tzxx.append(",sum(t1.TCJE-ifnull(t3.HB_JE,0))
			// JTBZ,sum(round(((t1.TCJE-ifnull(t3.HB_JE,0))/365*t2.QX),2))
			// JNHZ,sum(round(((t1.TCJE-ifnull(t3.HB_JE,0))/12*t2.QX_MONTH),2))
			// JNHZ_MONTH");
			sql_tzxx.append(
					"SELECT sum(t1.TCJE) TBYZ,sum(round((t1.TCJE/12*t2.QX_MONTH),2)) NHYZ_MONTH,sum(ifnull(t3.HB_JE,0)) HBDY");
			sql_tzxx.append(
					",sum(t1.TCJE-ifnull(t3.HB_JE,0)) JTBZ,sum(round(((t1.TCJE-ifnull(t3.HB_JE,0))/12*t2.QX_MONTH),2)) JNHZ_MONTH");
			sql_tzxx.append(
					",case when t2.XM_TYPE='01' then sum(round((t1.TCJE/365*datediff(adddate(t1.TZSJ,interval t2.QX_MONTH month),t1.TZSJ)),2)) when t2.XM_TYPE='02' then sum(round((t1.TCJE/365*t2.QX),2)) end  NHYZ");
			sql_tzxx.append(
					",case when t2.XM_TYPE='01' then sum(round(((t1.TCJE-ifnull(t3.HB_JE,0))/365*datediff(adddate(t1.TZSJ,interval t2.QX_MONTH month),t1.TZSJ)),2)) when t2.XM_TYPE='02' then sum(round(((t1.TCJE-ifnull(t3.HB_JE,0))/365*t2.QX),2)) end JNHZ");

			sql_tzxx.append(" FROM t_zhfw_tzrxx t1 left join t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID");
			sql_tzxx.append(" left join t_zhfw_hb t3 on t1.ID=t3.REF_TZRXX_ID and t3.HB_STATE='1'");
			sql_tzxx.append(" where date_format(t1.TZSJ,'%Y-%m-%d')=:tjDate and t1.TCZT='成功' and t1.USERID=:username");
			// logger.info("insertCacheTable sql_tzxx is " +
			// sql_tzxx.toString());
			map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_tzxx.toString(), paramMap));
			// 获取每个用户的充值信息:客户充值额,银盛确认额,通联确认额,充值核对
			StringBuffer sql_czxx = new StringBuffer();
			sql_czxx.append(
					"SELECT sum(t1.CZJE) KHCZE,case when t1.PLATFORM_TYPE like '01%' then sum(t2.CZJE) else 0 end YSQRE");
			sql_czxx.append(",case when t1.PLATFORM_TYPE like '02%' then sum(t2.CZJE) else 0 end TLQRE");
			sql_czxx.append(",t1.CZJE-t2.CZJE CZHD FROM t_zhfw_czjl_fhjg t2 left join t_zhfw_czjl t1 on t1.DDH=t2.DDH");
			sql_czxx.append(" where date_format(t1.CZSJ,'%Y-%m-%d')=:tjDate and t1.GYH=:username and t2.CZJG='充值成功'");
			// logger.info("insertCacheTable sql_czxx is " +
			// sql_czxx.toString());
			map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_czxx.toString(), paramMap));
			// 获取每个用户的提现信息:提现,提现手续费,提现支出合计
			StringBuffer sql_txxx = new StringBuffer();
			sql_txxx.append(
					"SELECT ifnull(sum(TXJE),0) TX,ifnull(sum(SXF),0) TXSXF,ifnull(sum(TXJE-SXF),0) TXZCHJ FROM t_zhfw_tx");
			sql_txxx.append(" where date_format(TXSJ,'%Y-%m-%d')=:tjDate and TX_STATE='01' and GYH=:username");
			// logger.info("insertCacheTable sql_txxx is " +
			// sql_txxx.toString());
			map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_txxx.toString(), paramMap));
			// 获取每个用户的注册信息:是否当前日期注册,是:1,否:0
			String sql_zcxx = "SELECT count(*) ZC FROM t_zhfw_user where date_format(CJRQ,'%Y-%m-%d')=:tjDate and DISABLED='1' and USER_NAME=:username";
			// logger.info("insertCacheTable sql_zcxx is " + sql_zcxx);
			map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_zcxx, paramMap));
			// 获取每个用户的归属信息:公司名称,门店名称
			map.putAll(this.getOaInfoByGyh(MapUtils.getString(map, "USER_NAME"), 3));
			// 把缓存信息插入缓存表
			StringBuffer sql_insertCache = new StringBuffer();
			sql_insertCache.append(
					"insert into t_zhfw_bb_tj(TJ_DATE,USER_NAME,TJR,TJJB,GSMC,MDMC,TBYZ,NHYZ,NHYZ_MONTH,HBDY,JTBZ,JNHZ,JNHZ_MONTH,KHCZE,YSQRE,TLQRE,CZHD,TX,TXSXF,TXZCHJ,ZC)");
			sql_insertCache.append(
					" values(:TJ_DATE,:USER_NAME,:TJR,:TJJB,:GSMC,:MDMC,:TBYZ,:NHYZ,:NHYZ_MONTH,:HBDY,:JTBZ,:JNHZ,:JNHZ_MONTH,:KHCZE,:YSQRE,:TLQRE,:CZHD,:TX,:TXSXF,:TXZCHJ,:ZC)");
			map.put("TJ_DATE", tjDate + " 00:00:00");
			// logger.info("insertCacheTable sql_insertCache is " +
			// sql_insertCache.toString());
			logger.info("insertCacheTable map is " + map);
			this.getNamedParameterJdbcTemplate().update(sql_insertCache.toString(), map);
		}
		// 补全公司名称,门店名称(没有数据的公司门店,插入一条空值)
		this.insertOaxx(tjDate);
		logger.info("insertCacheTable 成功生成报表缓存,日期:" + tjDate);
	}

	/**
	 * @作者 js
	 * @方法名 insertOaxx
	 * @功能 TODO 补全公司名称,门店名称(没有数据的公司门店,插入一条空值)
	 * @param tjDate
	 */
	private void insertOaxx(String tjDate) {
		logger.info("insertOaxx 补全公司和门店名称..." + tjDate);
		String sql_oaxx = "select DQ GSMC,SS MDMC from t_zhfw_oa_user group by DQ,SS";
		Map<String, Object> paramMap = new HashMap<>();
		List<Map<String, Object>> list = this.getNamedParameterJdbcTemplate().queryForList(sql_oaxx, paramMap);
		String sql_isbe = "select count(*) from t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate and GSMC=:GSMC and MDMC=:MDMC";
		String sql_insert = "insert into t_zhfw_bb_tj(TJ_DATE,GSMC,MDMC) values(:tjDateI,:GSMC,:MDMC)";
		for (Map<String, Object> map : list) {
			map.put("tjDate", tjDate);
			if (this.getNamedParameterJdbcTemplate().queryForInt(sql_isbe, map) == 0) {
				map.put("tjDateI", tjDate + " 00:00:00");
				this.getNamedParameterJdbcTemplate().update(sql_insert, map);
			}
		}
		sql_isbe = "select count(*) from t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate and GSMC is null and MDMC is null";
		paramMap.put("tjDate", tjDate);
		if (this.getNamedParameterJdbcTemplate().queryForInt(sql_isbe, paramMap) == 0) {
			paramMap.put("tjDateI", tjDate + " 00:00:00");
			paramMap.put("GSMC", null);
			paramMap.put("MDMC", null);
			this.getNamedParameterJdbcTemplate().update(sql_insert, paramMap);
		}
	}

	/**
	 * @作者 js
	 * @方法名 getOaInfoByGyh
	 * @功能 TODO 获取oa公司信息,根据用户名和推荐级别
	 * @param username
	 * @param tjjb
	 * @return
	 */
	private Map<String, Object> getOaInfoByGyh(String username, int tjjb) {
		logger.info("getOaInfoByGyh 获取用户推荐人,推荐级别信息..." + username);
		Map<String, Object> paramMap = new HashMap<>();
		Map<String, Object> result = new HashMap<>();
		String sql = "select t2.DQ GSMC,t2.SS MDMC,t1.TJR from t_zhfw_user t1 left join t_zhfw_oa_user t2 on t1.TJR=t2.GYH where t1.USER_NAME=:username";
		for (int i = 0; i < tjjb; i++) {
			paramMap.put("username", username);
			try {
				result = this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
			} catch (EmptyResultDataAccessException e) {
				result.put("GSMC", null);
				result.put("MDMC", null);
				result.put("TJR", null);
			}
			result.put("TJJB", null);
			if (MapUtils.isEmpty(result) || StringUtils.isBlank(MapUtils.getString(result, "TJR"))) {
				String sql_tg = "select t3.USER_NAME from t_zhfw_user_tgcode t1 left join t_zhfw_user_tgcode t2 on t1.TJR_TGCODE=t2.TGCODE left join t_zhfw_user t3 on t2.USER_NAME=t3.USER_NAME where t1.USER_NAME='"
						+ username + "'";
				String user_name = this.getSingleValue(sql_tg);
				if (StringUtils.isNotBlank(user_name)) {
					username = user_name;
				} else {
					break;
				}
			} else {
				result.put("TJJB", i + 1);
				break;
			}
		}
		return result;
	}

	/**
	 * @作者 js
	 * @方法名 getSingleValue
	 * @功能 TODO 根据sql获取单个字段值
	 * @param sSQL
	 * @return
	 */
	private String getSingleValue(String sSQL) {
		return this.getNamedParameterJdbcTemplate().query(sSQL, new ResultSetExtractor<String>() {
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String dx = "";
				// 获取字段数
				while (rs.next()) {
					dx = rs.getString(1);
				}
				return dx;
			}
		});
	}

	@SuppressWarnings("deprecation")
	@Override
	public List<Map<String, Object>> queryTjbbMonthList(String tjym) {
		// 验证数据缓存是否存在有效数据
		String tjyms[] = StringUtils.split(tjym, '-');
		tjyms[1] = String.format("%02d", Integer.parseInt(tjyms[1]));
		tjym = StringUtils.join(tjyms, '-');
		int tj_day = this.getDaysByYearMonth(Integer.parseInt(tjyms[0]), Integer.parseInt(tjyms[1]));
		Date now = new Date();
		if (Integer.parseInt(tjyms[0]) > (now.getYear() + 1900)) {// 如果年份大于当前年份,肯定无数据,返回空
			logger.info("queryTjbbMonthList 统计年份大于当前年份,无数据:" + tjym);
			return new ArrayList<>();
		} else if (Integer.parseInt(tjyms[0]) == (now.getYear() + 1900)) {// 如果年份等于当前年份,验证月份
			if (Integer.parseInt(tjyms[1]) > (now.getMonth() + 1)) {// 如果月份大于当前月份,肯定无数据,返回空
				logger.info("queryTjbbMonthList 统计月份大于当前月份,无数据:" + tjym);
				return new ArrayList<>();
			} else if (Integer.parseInt(tjyms[1]) == (now.getMonth() + 1)) {
				tj_day = now.getDate();
				logger.info("queryTjbbMonthList 开始生成截止当前日期的月报:" + StringUtils.join(tjyms, '-') + "-"
						+ String.format("%02d", tj_day));
			}
		}
		for (int i = 1; i <= tj_day; i++) {// 循环生成本月数据,截止统计日期
			String day = String.format("%02d", i);// 设置生成数据日期
			boolean isCacheTable = this.isCacheTable(StringUtils.join(tjyms, '-') + "-" + day);
			if (!isCacheTable) {// 如果不存在有效缓存数据,生成缓存表数据
				this.insertCacheTable(StringUtils.join(tjyms, '-') + "-" + day);
			}
		}
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		sql.append(
				"SELECT date_format(TJ_DATE,'%Y-%m') TJ_YM,ifnull(GSMC,'无归属') GSMC,ifnull(MDMC,'/') MDMC,sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(KHCZE,0)) KHCZE");
		sql.append(
				",sum(ifnull(YSQRE,0)) YSQRE,sum(ifnull(TLQRE,0)) TLQRE,sum(ifnull(CZHD,0)) CZHD,sum(ifnull(TX,0)) TX");
		sql.append(",sum(ifnull(TXSXF,0)) TXSXF,sum(ifnull(TXZCHJ,0)) TXZCHJ,sum(ifnull(ZC,0)) ZC FROM t_zhfw_bb_tj");
		sql.append(" where date_format(TJ_DATE,'%Y-%m')=:tjym");
		sql.append(" group by GSMC,MDMC order by CONVERT(GSMC USING gbk) desc");
		paramMap.put("tjym", tjym);
		logger.info("queryTjbbMonthList sql is " + sql.toString());
		logger.info("queryTjbbMonthList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public Map<String, Object> getSumTjbbMonth(String tjym) {
		// 验证数据缓存是否存在有效数据
		String tjyms[] = StringUtils.split(tjym, '-');
		tjyms[1] = String.format("%02d", Integer.parseInt(tjyms[1]));
		tjym = StringUtils.join(tjyms, '-');
		int tj_day = this.getDaysByYearMonth(Integer.parseInt(tjyms[0]), Integer.parseInt(tjyms[1]));
		Date now = new Date();
		if (Integer.parseInt(tjyms[0]) > (now.getYear() + 1900)) {// 如果年份大于当前年份,肯定无数据,返回空
			logger.info("getSumTjbbMonth 统计年份大于当前年份,无数据:" + tjym);
			return new HashMap<>();
		} else if (Integer.parseInt(tjyms[0]) == (now.getYear() + 1900)) {// 如果年份等于当前年份,验证月份
			if (Integer.parseInt(tjyms[1]) > (now.getMonth() + 1)) {// 如果月份大于当前月份,肯定无数据,返回空
				logger.info("getSumTjbbMonth 统计月份大于当前月份,无数据:" + tjym);
				return new HashMap<>();
			} else if (Integer.parseInt(tjyms[1]) == (now.getMonth() + 1)) {
				tj_day = now.getDate();
				logger.info("getSumTjbbMonth 开始生成截止当前日期的月报合计:" + StringUtils.join(tjyms, '-') + "-"
						+ String.format("%02d", tj_day));
			}
		}
		for (int i = 1; i <= tj_day; i++) {// 循环生成本月数据,截止统计日期
			String day = String.format("%02d", i);// 设置生成数据日期
			boolean isCacheTable = this.isCacheTable(StringUtils.join(tjyms, '-') + "-" + day);
			if (!isCacheTable) {// 如果不存在有效缓存数据,生成缓存表数据
				this.insertCacheTable(StringUtils.join(tjyms, '-') + "-" + day);
			}
		}
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		sql.append("SELECT sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(KHCZE,0)) KHCZE");
		sql.append(
				",sum(ifnull(YSQRE,0)) YSQRE,sum(ifnull(TLQRE,0)) TLQRE,sum(ifnull(CZHD,0)) CZHD,sum(ifnull(TX,0)) TX");
		sql.append(",sum(ifnull(TXSXF,0)) TXSXF,sum(ifnull(TXZCHJ,0)) TXZCHJ,sum(ifnull(ZC,0)) ZC FROM t_zhfw_bb_tj");
		sql.append(" where date_format(TJ_DATE,'%Y-%m')=:tjym");
		paramMap.put("tjym", tjym);
		logger.info("getSumTjbbMonth sql is " + sql.toString());
		logger.info("getSumTjbbMonth paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	/**
	 * @作者 js
	 * @方法名 getDaysByYearMonth
	 * @功能 TODO 根据年月获取当月天数
	 * @param year
	 * @param month
	 * @return
	 */
	private int getDaysByYearMonth(int year, int month) {
		Calendar a = Calendar.getInstance();
		a.set(Calendar.YEAR, year);
		a.set(Calendar.MONTH, month - 1);
		a.set(Calendar.DATE, 1);
		a.roll(Calendar.DATE, -1);
		int maxDate = a.get(Calendar.DATE);
		return maxDate;
	}

	@Override
	public List<Map<String, Object>> queryTjbbDayListByOa(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String tjDate = params.optString("tjDate");
		String gsmc = params.optString("gsmc");
		String mdmc = params.optString("mdmc");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		paramMap.put("tjym", StringUtils.substring(tjDate, 0, 7));
		String sql_t = "";
		if (StringUtils.equals(gsmc, "无归属") && StringUtils.equals(mdmc, "/")) {
			sql_t = "  AND GSMC is null AND MDMC is null";
		} else {
			sql_t = "  AND GSMC=:gsmc AND MDMC=:mdmc";
			paramMap.put("gsmc", gsmc);
			paramMap.put("mdmc", mdmc);
		}
		sql.append(
				"select t.* from (SELECT ID,USER_NAME,TJR,TJJB,sum(ifnull(TBYZ,0)) TBYZ_M,sum(ifnull(NHYZ,0)) NHYZ_M,sum(ifnull(JTBZ,0)) JTBZ_M,sum(ifnull(JNHZ,0)) JNHZ_M");
		sql.append(
				" FROM t_zhfw_bb_tj where USER_NAME is not null AND date_format(TJ_DATE,'%Y-%m')=:tjym AND date_format(TJ_DATE,'%Y-%m-%d')<=:tjDate");
		sql.append(sql_t);
		if (StringUtils.isNotEmpty(username)) {
			sql.append("  AND USER_NAME like :username");
			paramMap.put("username", username + "%");
		}
		sql.append(" group by USER_NAME) t where (TBYZ_M+NHYZ_M+JTBZ_M+JNHZ_M)>0");
		sql.append(" order by CONVERT(USER_NAME USING gbk) desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryTjbbDayListByOa sql is " + sql.toString());
		logger.info("queryTjbbDayListByOa paramMap is " + paramMap);
		List<Map<String, Object>> result = this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
		String sql_s = "SELECT ifnull(sum(TBYZ),0) TBYZ,ifnull(sum(NHYZ),0) NHYZ,ifnull(sum(HBDY),0) HBDY,ifnull(sum(JTBZ),0) JTBZ,ifnull(sum(JNHZ),0) JNHZ FROM t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m-%d')=:tjDate AND USER_NAME=:usernameMap";
		Iterator<Map<String, Object>> iterator = result.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = (Map<String, Object>) iterator.next();
			paramMap.put("usernameMap", MapUtils.getString(map, "USER_NAME"));
			map.putAll(this.getNamedParameterJdbcTemplate().queryForMap(sql_s, paramMap));
		}
		return result;
	}

	@Override
	public int getTjbbDayListByOaCount(JSONObject params) {
		String tjDate = params.optString("tjDate");
		String gsmc = params.optString("gsmc");
		String mdmc = params.optString("mdmc");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		paramMap.put("tjym", StringUtils.substring(tjDate, 0, 7));
		String sql_t = "";
		if (StringUtils.equals(gsmc, "无归属") && StringUtils.equals(mdmc, "/")) {
			sql_t = "  AND GSMC is null AND MDMC is null";
		} else {
			sql_t = "  AND GSMC=:gsmc AND MDMC=:mdmc";
			paramMap.put("gsmc", gsmc);
			paramMap.put("mdmc", mdmc);
		}
		sql.append(
				"select count(*) from (SELECT ID,USER_NAME,TJR,TJJB,sum(ifnull(TBYZ,0)) TBYZ_M,sum(ifnull(NHYZ,0)) NHYZ_M,sum(ifnull(JTBZ,0)) JTBZ_M,sum(ifnull(JNHZ,0)) JNHZ_M");
		sql.append(
				" FROM t_zhfw_bb_tj where USER_NAME is not null AND date_format(TJ_DATE,'%Y-%m')=:tjym AND date_format(TJ_DATE,'%Y-%m-%d')<=:tjDate");
		sql.append(sql_t);
		if (StringUtils.isNotEmpty(username)) {
			sql.append("  AND USER_NAME like :username");
			paramMap.put("username", username + "%");
		}
		sql.append(" group by USER_NAME) t where (TBYZ_M+NHYZ_M+JTBZ_M+JNHZ_M)>0");
		logger.info("getTjbbDayListByOaCount sql is " + sql.toString());
		logger.info("getTjbbDayListByOaCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryTjbbMonthListByOa(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String tjym = params.optString("tjym");
		String tjyms[] = StringUtils.split(tjym, '-');
		tjyms[1] = String.format("%02d", Integer.parseInt(tjyms[1]));
		tjym = StringUtils.join(tjyms, '-');
		String gsmc = params.optString("gsmc");
		String mdmc = params.optString("mdmc");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjym", tjym);
		String sql_t = "";
		if (StringUtils.equals(gsmc, "无归属") && StringUtils.equals(mdmc, "/")) {
			sql_t = "  AND GSMC is null AND MDMC is null";
		} else {
			sql_t = "  AND GSMC=:gsmc AND MDMC=:mdmc";
			paramMap.put("gsmc", gsmc);
			paramMap.put("mdmc", mdmc);
		}
		sql.append("select * from (SELECT ID,USER_NAME,TJR,TJJB,sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(KHCZE,0)) KHCZE");
		sql.append(
				",sum(ifnull(YSQRE,0)) YSQRE,sum(ifnull(TLQRE,0)) TLQRE,sum(ifnull(CZHD,0)) CZHD,sum(ifnull(TX,0)) TX");
		sql.append(
				",sum(ifnull(TXSXF,0)) TXSXF,sum(ifnull(TXZCHJ,0)) TXZCHJ FROM t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m')=:tjym and USER_NAME is not null");
		sql.append(sql_t);
		if (StringUtils.isNotEmpty(username)) {
			sql.append("  AND USER_NAME like :username");
			paramMap.put("username", username + "%");
		}
		sql.append(" group by USER_NAME) t where (TBYZ+HBDY+JTBZ+KHCZE+YSQRE+TLQRE+TX)>0");
		sql.append(" order by CONVERT(USER_NAME USING gbk) desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryTjbbMonthListByOa sql is " + sql.toString());
		logger.info("queryTjbbMonthListByOa paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getTjbbMonthListByOaCount(JSONObject params) {
		String tjym = params.optString("tjym");
		String tjyms[] = StringUtils.split(tjym, '-');
		tjyms[1] = String.format("%02d", Integer.parseInt(tjyms[1]));
		tjym = StringUtils.join(tjyms, '-');
		String gsmc = params.optString("gsmc");
		String mdmc = params.optString("mdmc");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjym", tjym);
		String sql_t = "";
		if (StringUtils.equals(gsmc, "无归属") && StringUtils.equals(mdmc, "/")) {
			sql_t = "  AND GSMC is null AND MDMC is null";
		} else {
			sql_t = "  AND GSMC=:gsmc AND MDMC=:mdmc";
			paramMap.put("gsmc", gsmc);
			paramMap.put("mdmc", mdmc);
		}
		sql.append(
				"select count(*) from (SELECT ID,USER_NAME,TJR,TJJB,sum(ifnull(TBYZ,0)) TBYZ,sum(ifnull(NHYZ,0)) NHYZ");
		sql.append(
				",sum(ifnull(HBDY,0)) HBDY,sum(ifnull(JTBZ,0)) JTBZ,sum(ifnull(JNHZ,0)) JNHZ,sum(ifnull(KHCZE,0)) KHCZE");
		sql.append(
				",sum(ifnull(YSQRE,0)) YSQRE,sum(ifnull(TLQRE,0)) TLQRE,sum(ifnull(CZHD,0)) CZHD,sum(ifnull(TX,0)) TX");
		sql.append(
				",sum(ifnull(TXSXF,0)) TXSXF,sum(ifnull(TXZCHJ,0)) TXZCHJ FROM t_zhfw_bb_tj where date_format(TJ_DATE,'%Y-%m')=:tjym and USER_NAME is not null");
		sql.append(sql_t);
		if (StringUtils.isNotEmpty(username)) {
			sql.append("  AND USER_NAME like :username");
			paramMap.put("username", username + "%");
		}
		sql.append(" group by USER_NAME) t where (TBYZ+HBDY+JTBZ+KHCZE+YSQRE+TLQRE+TX)>0");
		logger.info("getTjbbMonthListByOaCount sql is " + sql.toString());
		logger.info("getTjbbMonthListByOaCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> queryTjbbZzMap() {
		logger.info("queryTjbbZzMap 总账统计...,start " + new Date().getTime());
		Map<String, Object> result = new HashMap<>();
		NumberFormat df = new DecimalFormat("¥#,##0.00");
		NumberFormat df1 = new DecimalFormat("0.00%");
		NumberFormat df2 = new DecimalFormat("#,##0");
		// 注册总人数
		String sql_zc = "select count(*) ZCZRS from t_zhfw_user where DISABLED='1'";
		double zczrs = Double.valueOf(getSingleValue(sql_zc));
		result.put("ZCZRS", zczrs);
		// VIP充值总人数
		String sql_vip = "select count(*) VIPRS from t_zhfw_vipcz where VIP_STATE='01'";
		double viprs = Double.valueOf(getSingleValue(sql_vip));
		result.put("VIPRS", viprs);
		if (zczrs == 0) {
			result.put("VIPBL", df1.format(0));
		} else {
			result.put("VIPBL", df1.format(viprs / zczrs));
		}
		// 发标数据
		String sql_fb = "select ifnull(sum(JE),0) FBJE from t_zhfw_zqzrlb where 1=1";
		double fbje_all = Double.valueOf(getSingleValue(sql_fb));
		double fbje_jb = Double.valueOf(getSingleValue(sql_fb + " and ZT in ('0','2','3','4')"));
		double fbje_tzz = Double.valueOf(getSingleValue(sql_fb + " and ZT ='1'"));
		result.put("FBJE_ALL", df.format(fbje_all));
		result.put("FBJE_JB", df.format(fbje_jb));
		result.put("FBJE_TZZ", df.format(fbje_tzz));
		// 募集数据
		String sql_mj = "select ifnull(sum(t1.TCJE),0) MJJE from t_zhfw_tzrxx t1 left join t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT ='成功'";
		double tzje_all = Double.valueOf(getSingleValue(sql_mj));
		double tzje_jb = Double.valueOf(getSingleValue(sql_mj + " and t2.ZT in ('0','2','3','4')"));
		double tzje_tzz = Double.valueOf(getSingleValue(sql_mj + " and t2.ZT ='1'"));
		result.put("TZJE_ALL", df.format(tzje_all));
		result.put("TZJE_JB", df.format(tzje_jb));
		result.put("TZJE_TZZ", df.format(tzje_tzz));
		// 比例
		if (fbje_all == 0) {
			result.put("MJBL_ALL", df1.format(0));
		} else {
			result.put("MJBL_ALL", df1.format(tzje_all / fbje_all));
		}
		if (fbje_jb == 0) {
			result.put("MJBL_JB", df1.format(0));
		} else {
			result.put("MJBL_JB", df1.format(tzje_jb / fbje_jb));
		}
		if (fbje_tzz == 0) {
			result.put("MJBL_TZZ", df1.format(0));
		} else {
			result.put("MJBL_TZZ", df1.format(tzje_tzz / fbje_tzz));
		}
		// 充值信息
		String sql_cz = "select ifnull(sum(t2.CZJE),0) CZJE from t_zhfw_czjl_fhjg t1 left join t_zhfw_czjl t2 on t1.DDH=t2.DDH and t2.GYH is not null where t1.DDH is not null and t1.CZJG='充值成功'";
		double czje = Double.valueOf(getSingleValue(sql_cz));
		double czje_ys = Double.valueOf(getSingleValue(sql_cz + " and t2.PLATFORM_TYPE like '01%'"));
		double czje_tl = Double.valueOf(getSingleValue(sql_cz + " and t2.PLATFORM_TYPE like '02%'"));
		result.put("CZJE", df.format(czje));
		result.put("CZJE_YS", df.format(czje_ys));
		result.put("CZJE_TL", df.format(czje_tl));
		// 提现信息
		String sql_tx = "select ifnull(sum(TXJE),0) TXJE,ifnull(sum(SXF),0) TXSXF,ifnull(sum(TXJE-SXF),0) TXZC from t_zhfw_tx where TX_STATE='01'";
		Map<String, Object> result_tx = this.getNamedParameterJdbcTemplate().query(sql_tx,
				new ResultSetExtractor<Map<String, Object>>() {
					public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
						Map<String, Object> map = new HashMap<>();
						NumberFormat df = new DecimalFormat("¥#,##0.00");
						// 获取字段数
						while (rs.next()) {
							map.put("TXJE", df.format(Double.valueOf(rs.getString("TXJE"))));
							map.put("TXSXF", df.format(Double.valueOf(rs.getString("TXSXF"))));
							map.put("TXZC", df.format(Double.valueOf(rs.getString("TXZC"))));
						}
						return map;
					}
				});
		result.putAll(result_tx);
		// 账户信息
		String sql_zh = "select ifnull(sum(ZHYE),0) ZHYE,ifnull(sum(DJJE),0) DJJE,ifnull(sum(KYJE),0) KYJE from t_zhfw_jbxx";
		Map<String, Object> result_zh = this.getNamedParameterJdbcTemplate().query(sql_zh,
				new ResultSetExtractor<Map<String, Object>>() {
					public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
						Map<String, Object> map = new HashMap<>();
						NumberFormat df = new DecimalFormat("¥#,##0.00");
						// 获取字段数
						while (rs.next()) {
							map.put("ZHYE", df.format(Double.valueOf(rs.getString("ZHYE"))));
							map.put("DJJE", df.format(Double.valueOf(rs.getString("DJJE"))));
							map.put("KYJE", df.format(Double.valueOf(rs.getString("KYJE"))));
						}
						return map;
					}
				});
		result.putAll(result_zh);
		// 借款人应还款,截止当天项目截止的
		StringBuffer sql_yhk = new StringBuffer();
		sql_yhk.append(
				"select ifnull(sum(T.MJJE),0) YHKJE_BJ from (select sum(t1.TCJE) MJJE,case when t2.XM_TYPE = '01' and t2.ZT <> '1' ");
		sql_yhk.append(
				" and t2.JD = '100' then  date_format(date_add(max(t1.tzsj), interval t2.qx_month month),'%Y-%m-%d')  when t2.XM_TYPE = '02' ");
		sql_yhk.append(
				" and t2.ZT <> '1' then date_format(date_add(date_format(  date_add(t2.jxrq, interval -1 DAY), '%Y-%m-%d'), interval");
		sql_yhk.append(
				" t2.qx_month month),'%Y-%m-%d')  end YJ_ZZ_HK_DATE from t_zhfw_tzrxx t1, t_zhfw_zqzrlb t2  where t2.HK_TYPE = '02' ");
		sql_yhk.append(
				" and t1.ZQID = t2.ID  group by zqid) T where date_format(YJ_ZZ_HK_DATE,'%Y-%m-%d')<date_format(now(),'%Y-%m-%d')");
		double yhk_bj = Double.valueOf(getSingleValue(sql_yhk.toString()));
		StringBuffer sql_yhlx = new StringBuffer();
		sql_yhlx.append(
				"select ifnull(sum(round(T.TCJE*T.QXT*T.SJSYL/36500,2)),0) YHKJE_LX from (select t2.SJSYL,t1.TCJE,DATEDIFF(date_format(now(),'%Y-%m-%d')");
		sql_yhlx.append(",date_format(max(t1.TZSJ),'%Y-%m-%d')) QXT from t_zhfw_tzrxx t1, t_zhfw_zqzrlb t2  ");
		sql_yhlx.append("  where t2.HK_TYPE = '02' and t1.ZQID = t2.ID and t2.ZT<>'1'  group by t1.ID) T");
		double yhk_lx = Double.valueOf(getSingleValue(sql_yhlx.toString()));
		result.put("YHKJE", df.format(yhk_bj + yhk_lx));
		result.put("YHKJE_BJ", df.format(yhk_bj));
		result.put("YHKJE_LX", df.format(yhk_lx));
		// 借款人已还款信息
		String sql_ehk = "select ifnull(sum(YHK_JE),0) EHKJE from t_zhfw_jkr_hk where HK_STATE='01'";
		double ehkje = Double.valueOf(getSingleValue(sql_ehk));
		double ehkje_bj = Double.valueOf(getSingleValue(sql_ehk + " and HK_ZL='01'"));
		double ehkje_lx = Double.valueOf(getSingleValue(sql_ehk + " and HK_ZL='02'"));
		result.put("EHKJE", df.format(ehkje));
		result.put("EHKJE_BJ", df.format(ehkje_bj));
		result.put("EHKJE_LX", df.format(ehkje_lx));
		// 借款人未还款信息
		result.put("WHKJE", df.format(yhk_bj - ehkje_bj + yhk_lx - ehkje_lx));
		result.put("WHKJE_BJ", df.format(yhk_bj - ehkje_bj));
		result.put("WHKJE_LX", df.format(yhk_lx - ehkje_lx));
		// 投资人回款信息
		String sql_tzrhk = "select ifnull(sum(DB_HK_SYJE),0) DB_HK_SYJE from t_zhfw_tzrxx_dbtz_syhk where DB_HK_STATE='01'";
		String sql_tzrhk_bj = "select ifnull(sum(TCJE),0) TCJE from t_zhfw_tzrxx where TCZT='成功' and HKZT='3'";
		double hkje_tzr_sy = Double.valueOf(getSingleValue(sql_tzrhk));
		double hkje_tzr_bj = Double.valueOf(getSingleValue(sql_tzrhk_bj));
		result.put("HKJE_TZR", df.format(hkje_tzr_bj + hkje_tzr_sy));
		result.put("HKJE_TZR_BJ", df.format(hkje_tzr_bj));
		result.put("HKJE_TZR_SY", df.format(hkje_tzr_sy));
		// 投资人待回款金额
		result.put("DHKJE_TZR_BJ", df.format(tzje_all - hkje_tzr_bj));
		// 红包抵用投资金额
		String sql_hbdy = "select ifnull(sum(HB_JE),0) HBDYE from t_zhfw_hb where HB_STATE='1'";
		double hbdye = Double.valueOf(getSingleValue(sql_hbdy));
		result.put("HBDYE", df.format(hbdye));
		result.put("JTZZJE", df.format(tzje_all - hbdye));
		result.put("CZJE_TZR", df.format(czje - ehkje));
		// 借款人预计应还款信息
		StringBuffer sql_yhlx_yj = new StringBuffer();
		sql_yhlx_yj.append("select ifnull(sum(t1.TCJE*t2.SJSYL*t2.QX/36500),0) MJJE from t_zhfw_tzrxx t1 left join"
				+ " t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT ='成功'");
		double yhlx_yj = Double.valueOf(getSingleValue(sql_yhlx_yj.toString()));
		result.put("YHKJE_YJ", df.format(tzje_all + yhlx_yj));
		result.put("YHKJE_BJ_YJ", df.format(tzje_all));
		result.put("YHKJE_LX_YJ", df.format(yhlx_yj));
		// 用户点券账户信息
		StringBuffer sql_dq_ly = new StringBuffer();
		sql_dq_ly.append("select sum(DQ_JE) from t_zhfw_tg_dq");
		int dq_ly = Integer.valueOf(getSingleValue(sql_dq_ly.toString()));
		StringBuffer sql_dq_xf = new StringBuffer();
		sql_dq_xf.append("select sum(OCCUR_JE) from t_zhfw_dq_details_log where CHANGE_TYPE='1003'");
		int dq_xf = Integer.valueOf(getSingleValue(sql_dq_xf.toString()));
		StringBuffer sql_dq_ye = new StringBuffer();
		sql_dq_ye.append("select sum(DQ_JE) from t_zhfw_jbxx");
		int dq_ye = Integer.valueOf(getSingleValue(sql_dq_ye.toString()));
		result.put("DQJE_ZE", df2.format(dq_ly));
		result.put("DQJE_XF", df2.format(dq_xf));
		result.put("DQJE_YE", df2.format(dq_ye));
		logger.info("queryTjbbZzMap 总账统计...,end " + new Date().getTime());
		return result;
	}

	@Override
	public List<Map<String, Object>> querySyAndBjList(String type, String tjDate, Integer start, Integer limit) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ"
				+ ",t1.ZC_OCCUR_JE FFJE,t2.USERID USER_NAME,t2.ZQID ZQ_ID,t2.TCJE TZJE,t2.HTBH from t_zhfw_zc_details t1"
				+ " left join t_zhfw_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
				+ " and t1.ZC_CHANGE_TYPE =:typeZq" + " union "
				+ "select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
				+ ",t2.USERID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.TZJE,t2.HTBH from t_zhfw_zc_details t1"
				+ " left join t_zhfw_wdzr_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
				+ " and t1.ZC_CHANGE_TYPE =:typeWdzrTz");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		if (StringUtils.equals(type, "0x01")) {// 查询收益
			sql.append(") t");
			paramMap.put("typeZq", "1");
			paramMap.put("typeWdzrTz", "35");
		} else {// 查询本金,包含我的转让 转让人本金
			paramMap.put("typeZq", "11");
			paramMap.put("typeWdzrTz", "36");
			sql.append(
					" union select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
							+ ",t2.ZR_SQ_USER_ID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.ZR_WD_TZJE,t3.HTBH from t_zhfw_zc_details t1"
							+ " left join t_zhfw_wdzr t2 on t1.ZC_CHANGE_ID=T2.ID left join t_zhfw_tzrxx t3 on t2.ZR_REF_TZRXX_ID=t3.ID"
							+ " WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate and t1.ZC_CHANGE_TYPE =:typeWdzr) t");
			paramMap.put("typeWdzr", "37");
		}
		sql.append(" order by t.FFSJ desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("querySyAndBjList sql is " + sql.toString());
		logger.info("querySyAndBjList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getSyAndBjCount(String type, String tjDate) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select count(*) from (select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ"
						+ ",t1.ZC_OCCUR_JE FFJE,t2.USERID USER_NAME,t2.ZQID ZQ_ID,t2.TCJE TZJE,t2.HTBH from t_zhfw_zc_details t1"
						+ " left join t_zhfw_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
						+ " and t1.ZC_CHANGE_TYPE =:typeZq" + " union "
						+ "select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
						+ ",t2.USERID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.TZJE,t2.HTBH from t_zhfw_zc_details t1"
						+ " left join t_zhfw_wdzr_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
						+ " and t1.ZC_CHANGE_TYPE =:typeWdzrTz");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		if (StringUtils.equals(type, "0x01")) {// 查询收益
			sql.append(") t");
			paramMap.put("typeZq", "1");
			paramMap.put("typeWdzrTz", "35");
		} else {// 查询本金,包含我的转让 转让人本金
			paramMap.put("typeZq", "11");
			paramMap.put("typeWdzrTz", "36");
			sql.append(
					" union select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
							+ ",t2.ZR_SQ_USER_ID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.ZR_WD_TZJE,t3.HTBH from t_zhfw_zc_details t1"
							+ " left join t_zhfw_wdzr t2 on t1.ZC_CHANGE_ID=T2.ID left join t_zhfw_tzrxx t3 on t2.ZR_REF_TZRXX_ID=t3.ID"
							+ " WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate and t1.ZC_CHANGE_TYPE =:typeWdzr) t");
			paramMap.put("typeWdzr", "37");
		}
		logger.info("getSyAndBjCount sql is " + sql.toString());
		logger.info("getSyAndBjCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public String getSyAndBjSum(String type, String tjDate) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select sum(t.FFJE) FFJEZ from (select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ"
						+ ",t1.ZC_OCCUR_JE FFJE,t2.USERID USER_NAME,t2.ZQID ZQ_ID,t2.TCJE TZJE,t2.HTBH from t_zhfw_zc_details t1"
						+ " left join t_zhfw_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
						+ " and t1.ZC_CHANGE_TYPE =:typeZq" + " union "
						+ "select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
						+ ",t2.USERID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.TZJE,t2.HTBH from t_zhfw_zc_details t1"
						+ " left join t_zhfw_wdzr_tzrxx t2 on t1.ZC_CHANGE_ID=T2.ID WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate"
						+ " and t1.ZC_CHANGE_TYPE =:typeWdzrTz");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("tjDate", tjDate);
		if (StringUtils.equals(type, "0x01")) {// 查询收益
			sql.append(") t");
			paramMap.put("typeZq", "1");
			paramMap.put("typeWdzrTz", "35");
		} else {// 查询本金,包含我的转让 转让人本金
			paramMap.put("typeZq", "11");
			paramMap.put("typeWdzrTz", "36");
			sql.append(
					" union select t1.ID,t1.ZC_CHANGE_TYPE FFLY,date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d') FFSJ,t1.ZC_OCCUR_JE FFJE"
							+ ",t2.ZR_SQ_USER_ID USER_NAME,t2.ZR_REF_XM_ID ZQ_ID,t2.ZR_WD_TZJE,t3.HTBH from t_zhfw_zc_details t1"
							+ " left join t_zhfw_wdzr t2 on t1.ZC_CHANGE_ID=T2.ID left join t_zhfw_tzrxx t3 on t2.ZR_REF_TZRXX_ID=t3.ID"
							+ " WHERE date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d')=:tjDate and t1.ZC_CHANGE_TYPE =:typeWdzr) t");
			paramMap.put("typeWdzr", "37");
		}
		logger.info("getSyAndBjSum sql is " + sql.toString());
		logger.info("getSyAndBjSum paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				String result = "0.00";
				while (resultSet.next()) {
					result = resultSet.getString("FFJEZ");
				}
				return result;
			}
		});
	}

	@Override
	public Map<String, Object> queryTjbbZzMapSearch(String startDate, String endDate) {
		logger.info("queryTjbbZzMapSearch 总账统计,带条件...,start " + startDate + "-" + endDate);
		if (StringUtils.isBlank(startDate)) {
			startDate="2016-01-01";
		}
		if (StringUtils.isBlank(endDate)) {
			endDate=DateUtils.getCurrentDataStr();
		}
		Map<String, Object> result = new HashMap<>();
		NumberFormat df = new DecimalFormat("¥#,##0.00");
		NumberFormat df1 = new DecimalFormat("0.00%");
		NumberFormat df2 = new DecimalFormat("#,##0");
		// 注册总人数
		String sql_zc = "select count(*) ZCZRS from t_zhfw_user where DISABLED='1' and date_format(CJRQ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(CJRQ,'%Y-%m-%d')<='" + endDate + "'";
		double zczrs = Double.valueOf(getSingleValue(sql_zc));
		result.put("ZCZRS", zczrs);
		// VIP充值总人数
		String sql_vip = "select count(*) VIPRS from t_zhfw_vipcz where VIP_STATE='01' and date_format(CZSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(CZSJ,'%Y-%m-%d')<='" + endDate + "'";
		double viprs = Double.valueOf(getSingleValue(sql_vip));
		result.put("VIPRS", viprs);
		if (zczrs == 0) {
			result.put("VIPBL", df1.format(0));
		} else {
			result.put("VIPBL", df1.format(viprs / zczrs));
		}
		// 发标数据
		String sql_fb = "select ifnull(sum(JE),0) FBJE from t_zhfw_zqzrlb where date_format(FBSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(FBSJ,'%Y-%m-%d')<='" + endDate + "'";
		double fbje_all = Double.valueOf(getSingleValue(sql_fb));
		double fbje_jb = Double.valueOf(getSingleValue(sql_fb + " and ZT in ('0','2','3','4')"));
		double fbje_tzz = Double.valueOf(getSingleValue(sql_fb + " and ZT ='1'"));
		result.put("FBJE_ALL", df.format(fbje_all));
		result.put("FBJE_JB", df.format(fbje_jb));
		result.put("FBJE_TZZ", df.format(fbje_tzz));
		// 募集数据
		String sql_mj = "select ifnull(sum(t1.TCJE),0) MJJE from t_zhfw_tzrxx t1 left join t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT in ('成功','转让成功') and date_format(t1.TZSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(t1.TZSJ,'%Y-%m-%d')<='" + endDate + "'";
		double tzje_all = Double.valueOf(getSingleValue(sql_mj));
		double tzje_jb = Double.valueOf(getSingleValue(sql_mj + " and t2.ZT in ('0','2','3','4')"));
		double tzje_tzz = Double.valueOf(getSingleValue(sql_mj + " and t2.ZT ='1'"));
		result.put("TZJE_ALL", df.format(tzje_all));
		result.put("TZJE_JB", df.format(tzje_jb));
		result.put("TZJE_TZZ", df.format(tzje_tzz));
		// 比例
		if (fbje_all == 0) {
			result.put("MJBL_ALL", df1.format(0));
		} else {
			result.put("MJBL_ALL", df1.format(tzje_all / fbje_all));
		}
		if (fbje_jb == 0) {
			result.put("MJBL_JB", df1.format(0));
		} else {
			result.put("MJBL_JB", df1.format(tzje_jb / fbje_jb));
		}
		if (fbje_tzz == 0) {
			result.put("MJBL_TZZ", df1.format(0));
		} else {
			result.put("MJBL_TZZ", df1.format(tzje_tzz / fbje_tzz));
		}
		// 充值信息
		String sql_cz = "select ifnull(sum(t2.CZJE),0) CZJE from t_zhfw_czjl_fhjg t1 left join t_zhfw_czjl t2 on t1.DDH=t2.DDH and t2.GYH is not null where t1.DDH is not null and t1.CZJG='充值成功' and date_format(t2.CZSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(t2.CZSJ,'%Y-%m-%d')<='" + endDate + "'";
		double czje = Double.valueOf(getSingleValue(sql_cz));
		double czje_ys = Double.valueOf(getSingleValue(sql_cz + " and t2.PLATFORM_TYPE like '01%'"));
		double czje_tl = Double.valueOf(getSingleValue(sql_cz + " and t2.PLATFORM_TYPE like '02%'"));
		result.put("CZJE", df.format(czje));
		result.put("CZJE_YS", df.format(czje_ys));
		result.put("CZJE_TL", df.format(czje_tl));
		// 提现信息
		String sql_tx = "select ifnull(sum(TXJE),0) TXJE,ifnull(sum(SXF),0) TXSXF,ifnull(sum(TXJE-SXF),0) TXZC from t_zhfw_tx where TX_STATE='01' and date_format(TXSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(TXSJ,'%Y-%m-%d')<='" + endDate + "'";
		Map<String, Object> result_tx = this.getNamedParameterJdbcTemplate().query(sql_tx,
				new ResultSetExtractor<Map<String, Object>>() {
					public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
						Map<String, Object> map = new HashMap<>();
						NumberFormat df = new DecimalFormat("¥#,##0.00");
						// 获取字段数
						while (rs.next()) {
							map.put("TXJE", df.format(Double.valueOf(rs.getString("TXJE"))));
							map.put("TXSXF", df.format(Double.valueOf(rs.getString("TXSXF"))));
							map.put("TXZC", df.format(Double.valueOf(rs.getString("TXZC"))));
						}
						return map;
					}
				});
		result.putAll(result_tx);
		// 账户信息
		String sql_zh = "select ifnull(sum(ZHYE),0) ZHYE,ifnull(sum(DJJE),0) DJJE,ifnull(sum(KYJE),0) KYJE from t_zhfw_jbxx";
		Map<String, Object> result_zh = this.getNamedParameterJdbcTemplate().query(sql_zh,
				new ResultSetExtractor<Map<String, Object>>() {
					public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
						Map<String, Object> map = new HashMap<>();
						NumberFormat df = new DecimalFormat("¥#,##0.00");
						// 获取字段数
						while (rs.next()) {
							map.put("ZHYE", df.format(Double.valueOf(rs.getString("ZHYE"))));
							map.put("DJJE", df.format(Double.valueOf(rs.getString("DJJE"))));
							map.put("KYJE", df.format(Double.valueOf(rs.getString("KYJE"))));
						}
						return map;
					}
				});
		result.putAll(result_zh);
		// 借款人应还款,截止当天项目截止的,项目结束日期
		StringBuffer sql_yhk = new StringBuffer();
		sql_yhk.append("SELECT ifnull(sum(ZQ_MJJE),0) YHKJE_BJ FROM d_hd_p2p.t_zhfw_tzrxx_mjq_dk t1 left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID");
		sql_yhk.append(" WHERE t1.DK_STATE='01' and date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')<='"+endDate+"'");
		sql_yhk.append(" and date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')>='"+startDate+"'");
		logger.info("sql_yhk is "+sql_yhk.toString());
		double yhk_bj = Double.valueOf(getSingleValue(sql_yhk.toString()));
		StringBuffer sql_yhlx = new StringBuffer();
		sql_yhlx.append("select ifnull(sum(round(t.JKJE*t.SJSYL*if(t.QX_DAY>0,t.QX_DAY,0)/36500,2)),0) YHKJE_LX from");
		sql_yhlx.append( " (SELECT t2.SJSYL,t1.ZQ_MJJE JKJE,datediff(case when '"+endDate+"'>date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')");
		sql_yhlx.append(" then date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d') else '"+endDate+"' end");
		sql_yhlx.append( ",case when date_format(date_add('"+startDate+"',interval -1 day),'%Y-%m-%d')<date_format(t1.DK_DATE,'%Y-%m-%d') then date_format(t1.DK_DATE,'%Y-%m-%d') else date_format(date_add('"+startDate+"',interval -1 day),'%Y-%m-%d') end) QX_DAY");
		sql_yhlx.append( " FROM t_zhfw_tzrxx_mjq_dk t1 left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID WHERE t1.DK_STATE='01') t");
		logger.info("借款人应还款:"+sql_yhlx.toString());
		double yhk_lx = Double.valueOf(getSingleValue(sql_yhlx.toString()));
		result.put("YHKJE", df.format(yhk_bj + yhk_lx));
		result.put("YHKJE_BJ", df.format(yhk_bj));
		result.put("YHKJE_LX", df.format(yhk_lx));
		// 借款人已还款信息
		StringBuffer sql_ehkje_bj = new StringBuffer();
		sql_ehkje_bj.append("SELECT ifnull(sum(t3.YHK_JE),0) FROM d_hd_p2p.t_zhfw_tzrxx_mjq_dk t1");
		sql_ehkje_bj.append( " left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID left join t_zhfw_jkr_hk t3 on t3.REF_ZQ_ID=t2.ID");
		sql_ehkje_bj.append( " and t3.HK_STATE='01' and t3.HK_ZL='01' WHERE t1.DK_STATE='01' and");
		sql_ehkje_bj.append( " date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')<='"+endDate+"'");
		sql_ehkje_bj.append( " and date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')>='"+startDate+"'");
		StringBuffer sql_ehkje_lx = new StringBuffer();
		sql_ehkje_lx.append("select sum(YHKJE_LX) from (select case when '"+startDate+"'<=date_format(t1.DK_DATE,'%Y-%m-%d') and '"+endDate+"'>=");
		sql_ehkje_lx.append( "date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d') then sum(t3.YHK_JE) when");
		sql_ehkje_lx.append( " '"+startDate+"'<date_format(t1.DK_DATE,'%Y-%m-%d') and '"+endDate+"'<date_format(date_add(t1.DK_DATE");
		sql_ehkje_lx.append( ",interval t2.QX_MONTH month),'%Y-%m-%d') and '"+endDate+"'>=date_format(t1.DK_DATE,'%Y-%m-%d') then");
		sql_ehkje_lx.append( " case when (t1.ZQ_MJJE*t2.SJSYL*datediff('"+endDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)-sum(t3.YHK_JE)>0");
		sql_ehkje_lx.append( " then sum(t3.YHK_JE) else (t1.ZQ_MJJE*t2.SJSYL*datediff('"+endDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( " end when '"+startDate+"'>date_format(t1.DK_DATE,'%Y-%m-%d') and '"+startDate+"'<date_format(date_add(t1.DK_DATE");
		sql_ehkje_lx.append( ",interval t2.QX_MONTH month),'%Y-%m-%d') and '"+endDate+"'>=date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH");
		sql_ehkje_lx.append( " month),'%Y-%m-%d') then case when (t1.ZQ_MJJE*t2.SJSYL*datediff('"+startDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( "-sum(t3.YHK_JE)<=0 and (t1.ZQ_MJJE*t2.SJSYL*datediff(date_add(t1.DK_DATE,interval t2.QX_MONTH month),t1.DK_DATE)/36500)");
		sql_ehkje_lx.append( "-sum(t3.YHK_JE)>=0 then sum(t3.YHK_JE)-(t1.ZQ_MJJE*t2.SJSYL*datediff('"+startDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( " when (t1.ZQ_MJJE*t2.SJSYL*datediff('"+startDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)-sum(t3.YHK_JE)>0");
		sql_ehkje_lx.append( " then 0 else t1.ZQ_MJJE*t2.SJSYL*datediff(date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')");
		sql_ehkje_lx.append( " ,'"+startDate+"')/36500 end when '"+startDate+"'>date_format(t1.DK_DATE,'%Y-%m-%d') and '"+startDate+"'<date_format(date_add(t1.DK_DATE");
		sql_ehkje_lx.append( ",interval t2.QX_MONTH month),'%Y-%m-%d') and '"+endDate+"'<date_format(date_add(t1.DK_DATE,interval t2.QX_MONTH month),'%Y-%m-%d')");
		sql_ehkje_lx.append( " then case when (t1.ZQ_MJJE*t2.SJSYL*datediff('"+startDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( "-sum(t3.YHK_JE)<0 and (t1.ZQ_MJJE*t2.SJSYL*datediff('"+endDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( "-sum(t3.YHK_JE)>0 then (t1.ZQ_MJJE*t2.SJSYL*datediff('"+endDate+"',date_format(t1.DK_DATE,'%Y-%m-%d'))/36500)");
		sql_ehkje_lx.append( "-sum(t3.YHK_JE) else t1.ZQ_MJJE*t2.SJSYL*datediff('"+endDate+"','"+startDate+"')/36500 end else 0 end YHKJE_LX");
		sql_ehkje_lx.append( " from t_zhfw_tzrxx_mjq_dk t1 left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID left join t_zhfw_jkr_hk t3 on");
		sql_ehkje_lx.append( " t3.REF_ZQ_ID=t1.ZQ_ID where DK_STATE='01' group by t1.ZQ_ID) t");
		logger.info("已还款利息:"+sql_ehkje_lx.toString());
		double ehkje_bj = Double.valueOf(getSingleValue(sql_ehkje_bj.toString()));
		double ehkje_lx = Double.valueOf(getSingleValue(sql_ehkje_lx.toString()));
		result.put("EHKJE", df.format(ehkje_bj+ehkje_lx));
		result.put("EHKJE_BJ", df.format(ehkje_bj));
		result.put("EHKJE_LX", df.format(ehkje_lx));
		// 借款人未还款信息
		result.put("WHKJE", df.format(yhk_bj - ehkje_bj + yhk_lx - ehkje_lx));
		result.put("WHKJE_BJ", df.format(yhk_bj - ehkje_bj));
		result.put("WHKJE_LX", df.format(yhk_lx - ehkje_lx));
		// 投资人回款信息
		String sql_tzrhk = "select ifnull(sum(DB_HK_SYJE),0) DB_HK_SYJE from t_zhfw_tzrxx_dbtz_syhk where DB_HK_STATE='01' and date_format(DB_HK_DATE,'%Y-%m-%d')<='"+endDate+"' and date_format(DB_HK_DATE,'%Y-%m-%d')>='"+startDate+"'";
		String sql_tzrhk_bj = "select ifnull(sum(ZC_OCCUR_JE),0) from t_zhfw_zc_details where ZC_CHANGE_TYPE in ('11','36','37') and date_format(ZC_CHANGE_DATE,'%Y-%m-%d')<='"+endDate+"' and date_format(ZC_CHANGE_DATE,'%Y-%m-%d')>='"+startDate+"'";
		logger.info("投资人回款:"+sql_tzrhk.toString());
		double hkje_tzr_sy = Double.valueOf(getSingleValue(sql_tzrhk));
		double hkje_tzr_bj = Double.valueOf(getSingleValue(sql_tzrhk_bj));
		result.put("HKJE_TZR", df.format(hkje_tzr_bj + hkje_tzr_sy));
		result.put("HKJE_TZR_BJ", df.format(hkje_tzr_bj));
		result.put("HKJE_TZR_SY", df.format(hkje_tzr_sy));
		// 投资人待回款金额
		result.put("DHKJE_TZR_BJ", df.format(tzje_all - hkje_tzr_bj));
		// 红包抵用投资金额
		String sql_hbdy = "select ifnull(sum(HB_JE),0) HBDYE from t_zhfw_hb where HB_STATE='1' and date_format(HB_USE_DATE,'%Y-%m-%d')<='"+endDate+"' and date_format(HB_USE_DATE,'%Y-%m-%d')>='"+startDate+"'";
		double hbdye = Double.valueOf(getSingleValue(sql_hbdy));
		result.put("HBDYE", df.format(hbdye));
		result.put("JTZZJE", df.format(tzje_all - hbdye));
		result.put("CZJE_TZR", df.format(czje - ehkje_bj-ehkje_lx));
		// 借款人预计应还款信息
		StringBuffer sql_yhlx_yj = new StringBuffer();
		sql_yhlx_yj.append("select ifnull(sum(t1.TCJE*t2.SJSYL*t2.QX/36500),0) MJJE from t_zhfw_tzrxx t1 left join"
				+ " t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT ='成功' and date_format(t1.TZSJ,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(t1.TZSJ,'%Y-%m-%d')<='" + endDate + "'");
		double yhlx_yj = Double.valueOf(getSingleValue(sql_yhlx_yj.toString()));
		result.put("YHKJE_YJ", df.format(tzje_all + yhlx_yj));
		result.put("YHKJE_BJ_YJ", df.format(tzje_all));
		result.put("YHKJE_LX_YJ", df.format(yhlx_yj));
		// 用户点券账户信息
		StringBuffer sql_dq_ly = new StringBuffer();
		sql_dq_ly.append("select ifnull(sum(DQ_JE),0) from t_zhfw_tg_dq where date_format(LY_DATE,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(LY_DATE,'%Y-%m-%d')<='" + endDate + "'");
		int dq_ly = Integer.valueOf(getSingleValue(sql_dq_ly.toString()));
		StringBuffer sql_dq_xf = new StringBuffer();
		sql_dq_xf.append("select ifnull(sum(OCCUR_JE),0) from t_zhfw_dq_details_log where CHANGE_TYPE='1003' and date_format(CHANGE_DATE,'%Y-%m-%d')>='"
				+ startDate + "' and date_format(CHANGE_DATE,'%Y-%m-%d')<='" + endDate + "'");
		int dq_xf = Integer.valueOf(getSingleValue(sql_dq_xf.toString()));
		StringBuffer sql_dq_ye = new StringBuffer();
		sql_dq_ye.append("select ifnull(sum(DQ_JE),0) from t_zhfw_jbxx");
		int dq_ye = Integer.valueOf(getSingleValue(sql_dq_ye.toString()));
		result.put("DQJE_ZE", df2.format(dq_ly));
		result.put("DQJE_XF", df2.format(dq_xf));
		result.put("DQJE_YE", df2.format(dq_ye));
		logger.info("queryTjbbZzMap 总账统计...,end " + new Date().getTime());
		return result;
	}
	/**
	 * @作者 js  
	 * @方法名 insertCacheTableZZ  
	 * @功能 TODO 插入总账报表缓存数据,天为单位
	 * @param tjDate
	 */
	@Override
	public void insertCacheTableZZ(String gsDate) {
		logger.info("insertCacheTableZZ 生成总账报表缓存,日期:" + gsDate + "...");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("gsDate", gsDate);// 当前归属日期,查询用
		// 删除表中可能存在的缓存数据
		String sql_del = "delete from t_zhfw_bb_zz where date_format(GS_DATE,'%Y-%m-%d')=:gsDate";
		this.getNamedParameterJdbcTemplate().update(sql_del, paramMap);
		// 注册人数
		StringBuffer sql_zcrs = new StringBuffer();
		sql_zcrs.append("SELECT count(*) from t_zhfw_user where DISABLED='1' and date_format(CJRQ,'%Y-%m-%d')=:gsDate");
		int zcrs = this.getNamedParameterJdbcTemplate().queryForInt(sql_zcrs.toString(), paramMap);
		paramMap.put("ZC_RS", zcrs);
		//登陆人数
		StringBuffer sql_dlrs = new StringBuffer();
		sql_dlrs.append("select count(*) from (select USER_NAME from t_zhfw_login_log where date_format(LOGIN_DATE,'%Y-%m-%d')=:gsDate group by USER_NAME) t");
		int dlrs = this.getNamedParameterJdbcTemplate().queryForInt(sql_dlrs.toString(), paramMap);
		paramMap.put("DL_RS", dlrs);
		//升级VIP人数
		StringBuffer sql_viprs = new StringBuffer();
		sql_viprs.append("select count(*) from t_zhfw_vipcz where date_format(CZSJ,'%Y-%m-%d')=:gsDate and VIP_STATE='01'");
		int viprs = this.getNamedParameterJdbcTemplate().queryForInt(sql_viprs.toString(), paramMap);
		paramMap.put("SJ_VIP_RS", viprs);
		//实名认证人数
		StringBuffer sql_smrzrs = new StringBuffer();
		sql_smrzrs.append("select count(*) from t_zhfw_smrz where date_format(LR_DATE,'%Y-%m-%d')=:gsDate and RZ_STATE='01'");
		int smrzrs = this.getNamedParameterJdbcTemplate().queryForInt(sql_smrzrs.toString(), paramMap);
		paramMap.put("SMRZ_RS", smrzrs);
		//投资笔数
		StringBuffer sql_tzbs = new StringBuffer();
		sql_tzbs.append("select count(*) from t_zhfw_tzrxx where date_format(TZSJ,'%Y-%m-%d')=:gsDate and TCZT in ('成功','转让成功')");
		int tzbs = this.getNamedParameterJdbcTemplate().queryForInt(sql_tzbs.toString(), paramMap);
		paramMap.put("TZ_BS", tzbs);
		//申请借款笔数
		StringBuffer sql_sqjkbs = new StringBuffer();
		sql_sqjkbs.append("select count(*) from t_zhfw_zqzrlb_cg where date_format(SQSJ,'%Y-%m-%d')=:gsDate ");
		int sqjkbs = this.getNamedParameterJdbcTemplate().queryForInt(sql_sqjkbs.toString(), paramMap);
		paramMap.put("SQJK_BS", sqjkbs);
		//发标笔数
		StringBuffer sql_fbbs = new StringBuffer();
		sql_fbbs.append("select count(*) from t_zhfw_zqzrlb where date_format(FBSJ,'%Y-%m-%d')=:gsDate");
		int fbbs = this.getNamedParameterJdbcTemplate().queryForInt(sql_fbbs.toString(), paramMap);
		paramMap.put("FB_BS", fbbs);
		//发标金额
		StringBuffer sql_fbje = new StringBuffer();
		sql_fbje.append("select ifnull(sum(JE),0) FBJE from t_zhfw_zqzrlb where date_format(FBSJ,'%Y-%m-%d')=:gsDate");
		String fbje = this.getNamedParameterJdbcTemplate().query(sql_fbje.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String fbje="0.00";
				while (rs.next()) {
					fbje=rs.getString("FBJE");
				}
				return fbje;
			}
		});
		paramMap.put("FB_JE", fbje);
		//募集满打款金额-实际借款金额
		StringBuffer sql_dkje = new StringBuffer();
		sql_dkje.append("select ifnull(sum(ZQ_MJJE),0) MJQ_DK_JE from t_zhfw_tzrxx_mjq_dk where date_format(DK_DATE,'%Y-%m-%d')=:gsDate and DK_STATE='01'");
		String dkje = this.getNamedParameterJdbcTemplate().query(sql_dkje.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String dkje="0.00";
				while (rs.next()) {
					dkje=rs.getString("MJQ_DK_JE");
				}
				return dkje;
			}
		});
		paramMap.put("MJQ_DK_JE", dkje);
		//借款人当日应还本金金额
		StringBuffer sql_yhbj = new StringBuffer();
		sql_yhbj.append("select ifnull(sum(t1.ZQ_MJJE),0) YHBJ from t_zhfw_tzrxx_mjq_dk t1 left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID");
		sql_yhbj.append(" where date_format(date_add(date_add(t1.DK_DATE,interval 1 day),interval t2.QX_MONTH month),'%Y-%m-%d')");
		sql_yhbj.append("=:gsDate and DK_STATE='01'");
		String yhbj = this.getNamedParameterJdbcTemplate().query(sql_yhbj.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String yhbj="0.00";
				while (rs.next()) {
					yhbj=rs.getString("YHBJ");
				}
				return yhbj;
			}
		});
		paramMap.put("JKR_YH_BJ", yhbj);
		//借款人当日应还利息金额
		StringBuffer sql_yhlx = new StringBuffer();
		sql_yhlx.append("select ifnull(sum(round(t1.ZQ_MJJE*t2.SJSYL*datediff(:gsDate,date_add(:gsDate,interval -1 month))/36500,2)),0) YHLX");
		sql_yhlx.append(" from t_zhfw_tzrxx_mjq_dk t1 left join t_zhfw_zqzrlb t2 on t1.ZQ_ID=t2.ID where DK_STATE='01'");
		sql_yhlx.append(" and date_format(date_add(date_add(t1.DK_DATE,interval 1 day),interval period_diff(date_format(:gsDate,'%Y%m')");
		sql_yhlx.append(",date_format(date_add(t1.DK_DATE,interval 1 day),'%Y%m')) month),'%Y-%m-%d')=date_format(:gsDate,'%Y-%m-%d')");
		sql_yhlx.append(" and date_format(date_add(date_add(t1.DK_DATE,interval 1 day),interval t2.QX_MONTH month),'%Y-%m-%d')");
		sql_yhlx.append(">=date_format(:gsDate,'%Y-%m-%d') and date_format(date_add(t1.DK_DATE,interval 1 day),'%Y-%m-%d')");
		sql_yhlx.append("<=date_format(:gsDate,'%Y-%m-%d')");
		String yhlx = this.getNamedParameterJdbcTemplate().query(sql_yhlx.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String yhlx="0.00";
				while (rs.next()) {
					yhlx=rs.getString("YHLX");
				}
				return yhlx;
			}
		});
		paramMap.put("JKR_YH_LX", yhlx);
		//借款人当日偿还本金
		StringBuffer sql_ehbj = new StringBuffer();
		sql_ehbj.append("select ifnull(sum(YHK_JE),0) EHBJ from t_zhfw_jkr_hk where date_format(HK_DATE,'%Y-%m-%d')=:gsDate and HK_STATE='01' AND HK_ZL='01'");
		String ehbj = this.getNamedParameterJdbcTemplate().query(sql_ehbj.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String ehbj="0.00";
				while (rs.next()) {
					ehbj=rs.getString("EHBJ");
				}
				return ehbj;
			}
		});
		paramMap.put("JKR_EH_BJ", ehbj);
		//借款人当日偿还利息
		StringBuffer sql_ehlx = new StringBuffer();
		sql_ehlx.append("select ifnull(sum(YHK_JE),0) EHBJ from t_zhfw_jkr_hk where date_format(HK_DATE,'%Y-%m-%d')=:gsDate and HK_STATE='01' AND HK_ZL='02'");
		String ehlx = this.getNamedParameterJdbcTemplate().query(sql_ehlx.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String ehlx="0.00";
				while (rs.next()) {
					ehlx=rs.getString("EHBJ");
				}
				return ehlx;
			}
		});
		paramMap.put("JKR_EH_LX", ehlx);
		//当日投资金额
		StringBuffer sql_tzje = new StringBuffer();
		sql_tzje.append("select ifnull(sum(TCJE),0) TZJE from t_zhfw_tzrxx where date_format(TZSJ,'%Y-%m-%d')=:gsDate and TCZT in ('成功','转让成功')");
		String tzje = this.getNamedParameterJdbcTemplate().query(sql_tzje.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String tzje="0.00";
				while (rs.next()) {
					tzje=rs.getString("TZJE");
				}
				return tzje;
			}
		});
		paramMap.put("TZ_JE", tzje);
		//投资人当日应收本金
		StringBuffer sql_ysbj = new StringBuffer();
		sql_ysbj.append("select ifnull(sum(t.TCJE),0) YSBJ from (SELECT t1.TCJE,case when t2.XM_TYPE='01' then");
		sql_ysbj.append(" date_format(date_add(t1.TZSJ,interval t2.QX_MONTH month),'%Y-%m-%d') else");
		sql_ysbj.append(" date_format(date_add(date_add(t2.JXRQ,interval -1 day),interval t2.QX_MONTH month),'%Y-%m-%d') end DQRQ");
		sql_ysbj.append(" FROM t_zhfw_tzrxx t1 left join t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT in ('成功','转让成功')) t");
		sql_ysbj.append(" where t.DQRQ=:gsDate");
		String ysbj = this.getNamedParameterJdbcTemplate().query(sql_ysbj.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String ysbj="0.00";
				while (rs.next()) {
					ysbj=rs.getString("YSBJ");
				}
				return ysbj;
			}
		});
		paramMap.put("TZR_YS_BJ", ysbj);
		//投资人当日应收收益
		//正常投资
		StringBuffer sql_yssy = new StringBuffer();
		sql_yssy.append("select ifnull(sum(round(t.TCJE*t.SJSYL*datediff(t.YHKRQ,date_add(t.YHKRQ,interval -1 month))/36500,2)),0) YSSY");
		sql_yssy.append(" from (SELECT t1.TCJE,t2.SJSYL,case when t2.XM_TYPE='01' then t1.TZSJ else date_add(t2.JXRQ,interval -1 day) end JBRQ");
		sql_yssy.append(",case when t2.XM_TYPE='01' then date_add(t1.TZSJ,interval t2.QX_MONTH month) else");
		sql_yssy.append(" date_add(date_add(t2.JXRQ,interval -1 day),interval t2.QX_MONTH month) end DQRQ");
		sql_yssy.append(",date_format(date_add(case when t2.XM_TYPE='01' then t1.TZSJ else date_add(t2.JXRQ,interval -1 day) end");
		sql_yssy.append(",interval period_diff(date_format(:gsDate,'%Y%m'),date_format(case when t2.XM_TYPE='01' then t1.TZSJ else");
		sql_yssy.append(" date_add(t2.JXRQ,interval -1 day) end,'%Y%m')) month),'%Y-%m-%d') YHKRQ  FROM t_zhfw_tzrxx t1");
		sql_yssy.append(" left join t_zhfw_zqzrlb t2 on t1.ZQID=t2.ID where t1.TCZT ='成功') t where t.YHKRQ=date_format(:gsDate,'%Y-%m-%d')");
		sql_yssy.append(" and date_format(t.JBRQ,'%Y-%m-%d')<=date_format(:gsDate,'%Y-%m-%d') and date_format(t.DQRQ,'%Y-%m-%d')>=date_format(:gsDate,'%Y-%m-%d')");
		String yssy = this.getNamedParameterJdbcTemplate().query(sql_yssy.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String yssy="0.00";
				while (rs.next()) {
					yssy=rs.getString("YSSY");
				}
				return yssy;
			}
		});
		//我的转让投资
		StringBuffer sql_yssy_wdzr = new StringBuffer();
		sql_yssy_wdzr.append("select ifnull(sum(round(TZJE*NH_SYL*QX_DAY/36500,2)),0) YSSY from t_zhfw_wdzr_tzrxx where TZZT='成功' and date_format(DQRQ,'%Y-%m-%d')=:gsDate");
		String yssy_wdzr = this.getNamedParameterJdbcTemplate().query(sql_yssy_wdzr.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String yssy_wdzr="0.00";
				while (rs.next()) {
					yssy_wdzr=rs.getString("YSSY");
				}
				return yssy_wdzr;
			}
		});
		paramMap.put("TZR_YS_SY", NumberUtil.add(Double.valueOf(yssy), Double.valueOf(yssy_wdzr)));
		//投资人当日已收本金
		StringBuffer sql_esbj = new StringBuffer();
		sql_esbj.append("select ifnull(sum(ZC_OCCUR_JE),0) ESBJ from t_zhfw_zc_details where ZC_CHANGE_TYPE in ('11','36') and date_format(ZC_CHANGE_DATE,'%Y-%m-%d')=:gsDate");
		String esbj = this.getNamedParameterJdbcTemplate().query(sql_esbj.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String esbj="0.00";
				while (rs.next()) {
					esbj=rs.getString("ESBJ");
				}
				return esbj;
			}
		});
		paramMap.put("TZR_ES_BJ", esbj);
		//投资人当日已收收益
		StringBuffer sql_essy = new StringBuffer();
		sql_essy.append("select ifnull(sum(DB_HK_SYJE),0) ESSY from t_zhfw_tzrxx_dbtz_syhk where DB_HK_STATE='01' and date_format(DB_HK_DATE,'%Y-%m-%d')=:gsDate");
		String essy = this.getNamedParameterJdbcTemplate().query(sql_essy.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String essy="0.00";
				while (rs.next()) {
					essy=rs.getString("ESSY");
				}
				return essy;
			}
		});
		paramMap.put("TZR_ES_SY", essy);
		//当日红包发放金额
		StringBuffer sql_zshb = new StringBuffer();
		sql_zshb.append("select ifnull(sum(HB_JE),0) ZSHB from t_zhfw_hb where date_format(HB_CREATE_DATE,'%Y-%m-%d')=:gsDate");
		String zshb = this.getNamedParameterJdbcTemplate().query(sql_zshb.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String zshb="0.00";
				while (rs.next()) {
					zshb=rs.getString("ZSHB");
				}
				return zshb;
			}
		});
		paramMap.put("HB_ZS_JE", zshb);
		//当日红包抵用金额
		StringBuffer sql_dyhb = new StringBuffer();
		sql_dyhb.append("select ifnull(sum(HB_JE),0) DYHB from t_zhfw_hb where HB_STATE ='1' and date_format(HB_USE_DATE,'%Y-%m-%d')=:gsDate");
		String dyhb = this.getNamedParameterJdbcTemplate().query(sql_dyhb.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String dyhb="0.00";
				while (rs.next()) {
					dyhb=rs.getString("DYHB");
				}
				return dyhb;
			}
		});
		paramMap.put("HB_DY_JE", dyhb);
		//当日红包过期金额
		StringBuffer sql_gqhb = new StringBuffer();
		sql_gqhb.append("select ifnull(sum(HB_JE),0) GQHB from t_zhfw_hb where date_format(HB_OVER_DATE,'%Y-%m-%d')=:gsDate");
		String gqhb = this.getNamedParameterJdbcTemplate().query(sql_gqhb.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String gqhb="0.00";
				while (rs.next()) {
					gqhb=rs.getString("GQHB");
				}
				return gqhb;
			}
		});
		paramMap.put("HB_GQ_JE", gqhb);
		//当日点券发放金额
		StringBuffer sql_zsdq = new StringBuffer();
		sql_zsdq.append("select ifnull(sum(DQ_JE),0) ZSDQ from t_zhfw_tg_dq where date_format(LY_DATE,'%Y-%m-%d')=:gsDate");
		String zsdq = this.getNamedParameterJdbcTemplate().query(sql_zsdq.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String zsdq="0.00";
				while (rs.next()) {
					zsdq=rs.getString("ZSDQ");
				}
				return zsdq;
			}
		});
		paramMap.put("DQ_ZS_SE", zsdq);
		//当日点券消费金额
		StringBuffer sql_xfdq = new StringBuffer();
		sql_xfdq.append("select ifnull(sum(OCCUR_JE),0) XFDQ from t_zhfw_dq_details_log where CHANGE_TYPE='1003' and date_format(CHANGE_DATE,'%Y-%m-%d')=:gsDate");
		String xfdq = this.getNamedParameterJdbcTemplate().query(sql_xfdq.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String xfdq="0.00";
				while (rs.next()) {
					xfdq=rs.getString("XFDQ");
				}
				return xfdq;
			}
		});
		paramMap.put("DQ_XF_SE", xfdq);
		//账户充值金额
		StringBuffer sql_czje = new StringBuffer();
		sql_czje.append("select ifnull(sum(t1.CZJE),0) CZJE from t_zhfw_czjl_fhjg t1 left join t_zhfw_czjl t2 on t1.DDH=t2.DDH where date_format(t2.CZSJ,'%Y-%m-%d')=:gsDate");
		String czje = this.getNamedParameterJdbcTemplate().query(sql_czje.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String czje="0.00";
				while (rs.next()) {
					czje=rs.getString("CZJE");
				}
				return czje;
			}
		});
		paramMap.put("ZH_CZ_JE", czje);
		//账户提现金额
		//手续费-提现
		StringBuffer sql_tx = new StringBuffer();
		sql_tx.append("select ifnull(sum(TXJE),0) ZH_TX_JE,ifnull(sum(SXF),0) SXF_TX from t_zhfw_tx where TX_STATE='01' and date_format(TXSJ,'%Y-%m-%d')=:gsDate");
		Map<String, Object> txMap = this.getNamedParameterJdbcTemplate().query(sql_tx.toString(),paramMap, new ResultSetExtractor<Map<String, Object>>() {

			@Override
			public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, Object> txMap=new HashMap<>();
				txMap.put("ZH_TX_JE", "0.00");
				txMap.put("SXF_TX", "0.00");
				while (rs.next()) {
					txMap.put("ZH_TX_JE", rs.getString("ZH_TX_JE"));
					txMap.put("SXF_TX", rs.getString("SXF_TX"));
				}
				return txMap;
			}
		});
		paramMap.putAll(txMap);
		//手续费-升级VIP
		StringBuffer sql_sxfvip = new StringBuffer();
		sql_sxfvip.append("select ifnull(sum(0),0) SJVIP from t_zhfw_vipcz where date_format(CZSJ,'%Y-%m-%d')=:gsDate");
		String sxfvip = this.getNamedParameterJdbcTemplate().query(sql_sxfvip.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String sxfvip="0.00";
				while (rs.next()) {
					sxfvip=rs.getString("SJVIP");
				}
				return sxfvip;
			}
		});
		paramMap.put("SXF_SJ_VIP", sxfvip);
		//手续费-我的转让
		StringBuffer sql_sxfwdzr = new StringBuffer();
		sql_sxfwdzr.append("select ifnull(sum(t2.ZR_SXF),0) ZRSXF from t_zhfw_wdzr_tzrxx t1 left join t_zhfw_wdzr t2 on t1.TZ_WDZR_ID=t2.ID where TZZT='成功' and date_format(DQRQ,'%Y-%m-%d')=:gsDate");
		String sxfwdzr = this.getNamedParameterJdbcTemplate().query(sql_sxfwdzr.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String sxfwdzr="0.00";
				while (rs.next()) {
					sxfwdzr=rs.getString("ZRSXF");
				}
				return sxfwdzr;
			}
		});
		paramMap.put("SXF_WDZR", sxfwdzr);
		// 把缓存信息插入缓存表
		StringBuffer sql_insertCache = new StringBuffer();
		sql_insertCache.append("insert into t_zhfw_bb_zz(GS_DATE,ZC_RS,DL_RS,SJ_VIP_RS,SMRZ_RS,TZ_BS,SQJK_BS");
		sql_insertCache.append(",FB_BS,FB_JE,MJQ_DK_JE,JKR_YH_BJ,JKR_YH_LX,JKR_EH_BJ,JKR_EH_LX,TZ_JE,TZR_YS_BJ,TZR_YS_SY");
		sql_insertCache.append(",TZR_ES_BJ,TZR_ES_SY,HB_ZS_JE,HB_DY_JE,HB_GQ_JE,DQ_ZS_SE,DQ_XF_SE,ZH_CZ_JE,ZH_TX_JE,SXF_TX");
		sql_insertCache.append(",SXF_SJ_VIP,SXF_WDZR,CREATE_DATE) values(:GS_DATE,:ZC_RS,:DL_RS,:SJ_VIP_RS,:SMRZ_RS,:TZ_BS,:SQJK_BS");
		sql_insertCache.append(",:FB_BS,:FB_JE,:MJQ_DK_JE,:JKR_YH_BJ,:JKR_YH_LX,:JKR_EH_BJ,:JKR_EH_LX,:TZ_JE,:TZR_YS_BJ,:TZR_YS_SY");
		sql_insertCache.append(",:TZR_ES_BJ,:TZR_ES_SY,:HB_ZS_JE,:HB_DY_JE,:HB_GQ_JE,:DQ_ZS_SE,:DQ_XF_SE,:ZH_CZ_JE,:ZH_TX_JE,:SXF_TX");
		sql_insertCache.append(",:SXF_SJ_VIP,:SXF_WDZR,now())");
		paramMap.put("GS_DATE", gsDate + " 00:00:00");
		this.getNamedParameterJdbcTemplate().update(sql_insertCache.toString(), paramMap);
		logger.info("insertCacheTableZZ 生成总账报表缓存,日期:" + gsDate + "end");
	}

	@Override
	public void saveSysDateSort(String startDate,String endDate) {
		String sql="call p_auto_set_sys_date(:startDate,:endDate)";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		this.getNamedParameterJdbcTemplate().update(sql, paramMap);
		
	}

	@Override
	public List<String> listNotCacheZzDate(String startDate,String endDate) {
		String sql0="delete from t_zhfw_bb_zz where date_format(GS_DATE,'%Y-%m-%d')>=date_format(CREATE_DATE,'%Y-%m-%d')";
		Map<String, Object> paramMap=new HashMap<>();
		this.getNamedParameterJdbcTemplate().update(sql0,paramMap);
		String sql="select date_format(t1.TJ_DATE,'%Y-%m-%d') TJ_DATE from t_zhfw_sys_date t1"
				+ " left join t_zhfw_bb_zz t2 on t1.TJ_DATE=t2.GS_DATE where t2.GS_DATE is null"
				+ " and date_format(t1.TJ_DATE,'%Y-%m-%d')>=:startDate and date_format(t1.TJ_DATE,'%Y-%m-%d')<=:endDate";
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		logger.info("listNotCacheZzDate sql is "+sql);
		logger.info("listNotCacheZzDate paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql, paramMap,new ResultSetExtractor<List<String>>() {

			@Override
			public List<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<String> result=new ArrayList<>();
				while (rs.next()) {
					result.add(rs.getString("TJ_DATE"));
				}
				return result;
			}
		});
	}

	@Override
	public Map<String, String> getZzMap(String startDate, String endDate) {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT ifnull(sum(ZC_RS),0) ZC_RS,ifnull(sum(DL_RS),0) DL_RS,ifnull(sum(SJ_VIP_RS),0) SJ_VIP_RS");
		sql.append(",ifnull(sum(SMRZ_RS),0) SMRZ_RS,ifnull(sum(TZ_BS),0) TZ_BS,ifnull(sum(SQJK_BS),0) SQJK_BS");
		sql.append(",ifnull(sum(FB_BS),0) FB_BS,ifnull(sum(FB_JE),0) FB_JE,ifnull(sum(MJQ_DK_JE),0) MJQ_DK_JE");
		sql.append(",ifnull(sum(JKR_YH_BJ),0) JKR_YH_BJ,ifnull(sum(JKR_YH_LX),0) JKR_YH_LX,ifnull(sum(JKR_EH_BJ),0) JKR_EH_BJ");
		sql.append(",ifnull(sum(JKR_EH_LX),0) JKR_EH_LX,ifnull(sum(TZ_JE),0) TZ_JE,ifnull(sum(TZR_YS_BJ),0) TZR_YS_BJ");
		sql.append(",ifnull(sum(TZR_YS_SY),0) TZR_YS_SY,ifnull(sum(TZR_ES_BJ),0) TZR_ES_BJ,ifnull(sum(TZR_ES_SY),0) TZR_ES_SY");
		sql.append(",ifnull(sum(HB_ZS_JE),0) HB_ZS_JE,ifnull(sum(HB_DY_JE),0) HB_DY_JE,ifnull(sum(HB_GQ_JE),0) HB_GQ_JE");
		sql.append(",ifnull(sum(DQ_ZS_SE),0) DQ_ZS_SE,ifnull(sum(DQ_XF_SE),0) DQ_XF_SE,ifnull(sum(ZH_CZ_JE),0) ZH_CZ_JE");
		sql.append(",ifnull(sum(ZH_TX_JE),0) ZH_TX_JE,ifnull(sum(SXF_TX),0) SXF_TX,ifnull(sum(SXF_SJ_VIP),0) SXF_SJ_VIP");
		sql.append(",ifnull(sum(SXF_WDZR),0) SXF_WDZR FROM t_zhfw_bb_zz");
		sql.append(" where date_format(GS_DATE,'%Y-%m-%d')>=:startDate and date_format(GS_DATE,'%Y-%m-%d')<=:endDate");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		logger.info("getZzMap sql is "+sql.toString());
		logger.info("getZzMap paramMap is "+paramMap);
		String sql_zh = "select ifnull(sum(ZHYE),0) ZHYE,ifnull(sum(DJJE),0) DJJE,ifnull(sum(KYJE),0) KYJE from t_zhfw_jbxx";
		Map<String, String> result_zh = this.getNamedParameterJdbcTemplate().query(sql_zh,
				new ResultSetExtractor<Map<String, String>>() {
					public Map<String, String> extractData(ResultSet rs) throws SQLException, DataAccessException {
						Map<String, String> map = new HashMap<>();
						NumberFormat df = new DecimalFormat("¥#,##0.00");
						// 获取字段数
						while (rs.next()) {
							map.put("ZHYE", df.format(Double.valueOf(rs.getString("ZHYE"))));
							map.put("DJJE", df.format(Double.valueOf(rs.getString("DJJE"))));
							map.put("KYJE", df.format(Double.valueOf(rs.getString("KYJE"))));
						}
						return map;
					}
				});
		Map<String, String> result= this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<Map<String, String>>() {

			@Override
			public Map<String, String> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, String> result=new HashMap<>();
				NumberFormat df = new DecimalFormat("¥#,##0.00");
				NumberFormat df1 = new DecimalFormat("0.00%");
				NumberFormat df2 = new DecimalFormat("#,##0");
				while (rs.next()) {
					result.put("ZC_RS", df2.format(rs.getInt("ZC_RS")));//注册总人数
					result.put("DL_RS", df2.format(rs.getInt("DL_RS")));//登陆总人次
					result.put("SJ_VIP_RS", df2.format(rs.getInt("SJ_VIP_RS")));//VIP人数
					result.put("SMRZ_RS", df2.format(rs.getInt("SMRZ_RS")));//实名认证人数
					result.put("TZ_BS", df2.format(rs.getInt("TZ_BS")));//投资笔数
					result.put("SQJK_BS", df2.format(rs.getInt("SQJK_BS")));//发标申请笔数
					result.put("FB_BS", df2.format(rs.getInt("FB_BS")));//发标笔数
					
					result.put("FB_JE", df.format(rs.getDouble("FB_JE")));
					result.put("MJQ_DK_JE", df.format(rs.getDouble("MJQ_DK_JE")));
					result.put("ZBZ_JE", df.format(NumberUtil.sub(rs.getDouble("FB_JE"), rs.getDouble("MJQ_DK_JE"))));
					result.put("JKR_YH_BJ", df.format(rs.getDouble("JKR_YH_BJ")));
					result.put("JKR_EH_BJ", df.format(rs.getDouble("JKR_EH_BJ")));
					result.put("JKR_WH_BJ", df.format(NumberUtil.sub(rs.getDouble("JKR_YH_BJ"), rs.getDouble("JKR_EH_BJ"))));
					result.put("JKR_YH_LX", df.format(rs.getDouble("JKR_YH_LX")));
					result.put("JKR_EH_LX", df.format(rs.getDouble("JKR_EH_LX")));
					result.put("JKR_WH_LX", df.format(NumberUtil.sub(rs.getDouble("JKR_YH_LX"), rs.getDouble("JKR_EH_LX"))));
					
					result.put("TZ_JE", df.format(rs.getDouble("TZ_JE")));
					result.put("TZR_YS_BJ", df.format(rs.getDouble("TZR_YS_BJ")));
					result.put("TZR_ES_BJ", df.format(rs.getDouble("TZR_ES_BJ")));
					result.put("TZR_YS_SY", df.format(rs.getDouble("TZR_YS_SY")));
					result.put("TZR_ES_SY", df.format(rs.getDouble("TZR_ES_SY")));
					
					result.put("HB_ZS_JE", df.format(rs.getDouble("HB_ZS_JE")));
					result.put("HB_DY_JE", df.format(rs.getDouble("HB_DY_JE")));
					result.put("JTZ_JE", df.format(NumberUtil.sub(rs.getDouble("TZ_JE"), rs.getDouble("HB_DY_JE"))));
					result.put("HB_GQ_JE", df.format(rs.getDouble("HB_GQ_JE")));
					
					result.put("DQ_ZS_SE", df2.format(rs.getInt("DQ_ZS_SE")));
					result.put("DQ_XF_SE", df2.format(rs.getInt("DQ_XF_SE")));
					
					result.put("ZH_CZ_JE", df.format(rs.getDouble("ZH_CZ_JE")));
					result.put("ZH_TX_JE", df.format(rs.getDouble("ZH_TX_JE")));
					result.put("SXF_TX", df.format(rs.getDouble("SXF_TX")));
					result.put("SXF_SJ_VIP", df.format(rs.getDouble("SXF_SJ_VIP")));
					result.put("SXF_WDZR", df.format(rs.getDouble("SXF_WDZR")));
				}
				return result;
			}
		});
		result.putAll(result_zh);
		return result;
	}

	@Override
	public List<Map<String, Object>> listSysSy(JSONObject params) {
		int start=params.optInt("start");
		int limit=params.optInt("limit");
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String sylx=params.optString("sylx");
		StringBuffer sql=new StringBuffer();
		sql.append("select ID,SYS_SY_TYPE,SYS_SY_JE,REF_TABLE_NAME,REF_TABLE_ID,REF_USER_NAME,SYS_SY_COMMENT");
		sql.append(",date_format(SYS_SY_DATE,'%Y-%m-%d') SYS_SY_DATE from t_zhfw_sys_sy where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(sylx)) {
			sql.append(" and SYS_SY_TYPE=:sylx");
			paramMap.put("sylx", sylx);
		}
		sql.append(" order by date_format(SYS_SY_DATE,'%Y-%m-%d %H:%i:%s') desc");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listSysSy sql is "+sql.toString());
		logger.info("listSysSy paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getSysSyCount(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String sylx=params.optString("sylx");
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_sys_sy where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(sylx)) {
			sql.append(" and SYS_SY_TYPE=:sylx");
			paramMap.put("sylx", sylx);
		}
		logger.info("getSysSyCount sql is "+sql.toString());
		logger.info("getSysSyCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public String getSysSySumJe(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String sylx=params.optString("sylx");
		StringBuffer sql=new StringBuffer();
		sql.append("select ifnull(sum(SYS_SY_JE),0) SYZJE from t_zhfw_sys_sy where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(SYS_SY_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(sylx)) {
			sql.append(" and SYS_SY_TYPE=:sylx");
			paramMap.put("sylx", sylx);
		}
		logger.info("getSysSySumJe sql is "+sql.toString());
		logger.info("getSysSySumJe paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String re="¥0.00";
				NumberFormat df = new DecimalFormat("¥#,##0.00");
				while (rs.next()) {
					re=df.format(rs.getDouble("SYZJE"));
				}
				return re;
			}
		});
	}

}
